﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace WindowsService
{
    public class SqlHelper
    {
        public static int timeout = 180;

        //Database connection strings
        public static readonly string ConnectionStringPM = ConfigurationManager.AppSettings["ConnectionStringPM"];
        public static readonly string ConnectionStringSales = ConfigurationManager.AppSettings["ConnectionStringSales"];
        //
        /// <summary>
        /// Execute a SqlCommand using the provided parameters.
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="sqlText">the stored procedure name or SQL command</param>
        /// <param name="sqlParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string sqlText, params SqlParameter[] sqlParameters)
        {
            SqlCommand command = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    // Prepare a command for execution.
                    PrepareCommand(command, connection, null, cmdType, sqlText, sqlParameters);
                    int result = command.ExecuteNonQuery();
                    command.Parameters.Clear();
                    return result;
                }
                catch (SqlException e)
                {
                    LogHandle.WriteErrorLog(e);
                    throw new Exception(e.Message);
                }
                catch (Exception e)
                {
                    LogHandle.WriteErrorLog(e);
                    throw e;
                }
                finally
                {
                    if (connection != null)
                        connection.Close();
                }
            }
        }

        /// <summary>
        /// Execute a SqlCommand using an existing SQL Transaction 
        /// using the provided parameters.
        /// </summary>
        /// <param name="transaction">an existing sql transaction</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="sqlText">the stored procedure name or SQL command</param>
        /// <param name="sqlParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType cmdType, string sqlText, params SqlParameter[] sqlParameters)
        {
            try
            {
                SqlCommand command = new SqlCommand();

                // Prepare a command for execution.
                PrepareCommand(command, transaction.Connection, transaction, cmdType, sqlText, sqlParameters);
                int result = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return result;
            }
            catch (Exception e)
            {
                LogHandle.WriteErrorLog(e);
                throw e;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns a resultset using the provided parameters.
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="sqlText">the stored procedure name or SQL command</param>
        /// <param name="sqlParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>A SqlDataReader containing the results</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string sqlText, params SqlParameter[] sqlParameters)
        {
            SqlCommand command = new SqlCommand();
            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                // Prepare a command for execution.
                PrepareCommand(command, connection, null, cmdType, sqlText, sqlParameters);
                SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return dataReader;
            }
            catch (SqlException e)
            {
                if (connection != null)
                    connection.Close();
                LogHandle.WriteErrorLog(e);
                throw new Exception(e.Message);
            }
            catch (Exception e)
            {
                if (connection != null)
                    connection.Close();
                LogHandle.WriteErrorLog(e);
                throw e;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns the first column of the first record using the provided parameters.
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="sqlText">the stored procedure name or SQL command</param>
        /// <param name="sqlParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object in the first column of the first record according to the provided parameters.</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string sqlText, params SqlParameter[] sqlParameters)
        {
            SqlCommand command = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    // Prepare a command for execution.
                    PrepareCommand(command, connection, null, cmdType, sqlText, sqlParameters);
                    object result = command.ExecuteScalar();
                    command.Parameters.Clear();
                    return result;
                }
                catch (SqlException e)
                {
                    LogHandle.WriteErrorLog(e);
                    throw new Exception(e.Message);
                }
                catch (Exception e)
                {
                    LogHandle.WriteErrorLog(e);
                    throw e;
                }
                finally
                {
                    if (connection != null)
                        connection.Close();
                }
            }
        }

        /// <summary>
        /// Get sequence number from the new row.
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <returns>The new row's sequence number.</returns>
        public static long GetIdendtity(SqlConnection connection, SqlTransaction transaction)
        {

            SqlCommand command = new SqlCommand("SELECT @@Identity");
            command.Connection = connection;
            command.CommandTimeout = timeout;
            if (transaction != null)
                command.Transaction = transaction;

            try
            {
                object ident = command.ExecuteScalar();
                if (Convert.IsDBNull(ident))
                {
                    return 0;
                }
                else
                {
                    return Convert.ToInt64(ident);
                }
            }
            catch (SqlException e)
            {
                LogHandle.WriteErrorLog(e);
                throw new Exception(e.Message);
            }
            catch (Exception e)
            {
                LogHandle.WriteErrorLog(e);
                throw e;
            }
        }

        /// <summary>
        /// To get a dataSet that using the provided parameters.
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="sqlText">the stored procedure name or SQL command</param>
        /// <param name="sqlParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>dataSet</returns>
        public static DataSet GetDataSet(string connectionString, CommandType cmdType, string sqlText, params SqlParameter[] sqlParameters)
        {
            DataSet dataSet = new DataSet();
            SqlDataAdapter adapter;
            SqlCommand command = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    // Prepare a command for execution.
                    PrepareCommand(command, connection, null, cmdType, sqlText, sqlParameters);
                    adapter = new SqlDataAdapter(command);
                    adapter.Fill(dataSet);

                    return dataSet;
                }
                catch (SqlException e)
                {
                    LogHandle.WriteErrorLog(e);
                    throw new Exception(e.Message);
                }
                catch (Exception e)
                {
                    LogHandle.WriteErrorLog(e);
                    throw e;
                }
                finally
                {
                    if (connection != null)
                        connection.Close();

                    if (dataSet != null)
                        dataSet = null;
                }
            }
        }

        /// <summary>
        /// To get a datatable that using the provided parameters.
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="sqlText">the stored procedure name or SQL command</param>
        /// <param name="sqlParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>datatable</returns>
        public static DataTable GetDataTable(string connectionString, CommandType cmdType, string sqlText, params SqlParameter[] sqlParameters)
        {
            DataTable dataTable = new DataTable();
            SqlDataAdapter adapter;
            SqlCommand command = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    // Prepare a command for execution.
                    PrepareCommand(command, connection, null, cmdType, sqlText, sqlParameters);
                    adapter = new SqlDataAdapter(command);
                    adapter.Fill(dataTable);

                    return dataTable;
                }
                catch (SqlException e)
                {
                    LogHandle.WriteErrorLog(e);
                    throw new Exception(e.Message);
                }
                catch (Exception e)
                {
                    LogHandle.WriteErrorLog(e);
                    throw e;
                }
                finally
                {
                    if (connection != null)
                        connection.Close();

                    if (dataTable != null)
                        dataTable = null;
                }
            }
        }

       

        /// <summary>
        /// Prepare a command for execution.
        /// </summary>
        /// <param name="command">SqlCommand</param>
        /// <param name="connection">SqlConnection</param>
        /// <param name="transaction">SqlTransaction</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="sqlText">the stored procedure name or SQL command</param>
        /// <param name="sqlParameters">an array of SqlParamters used to execute the command</param>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection,
            SqlTransaction transaction, CommandType cmdType, string sqlText, params SqlParameter[] sqlParameters)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                command.Connection = connection;
                command.CommandText = sqlText;
                command.CommandTimeout = timeout;
                command.CommandType = cmdType;

                if (transaction != null)
                    command.Transaction = transaction;

                if (sqlParameters != null)
                {
                    foreach (SqlParameter parm in sqlParameters)
                        command.Parameters.Add(parm);
                }
            }
            catch (Exception e)
            {
                LogHandle.WriteErrorLog(e);
                throw e;
            }
        }
    }
}
